package com.tuanzi.loan.business.repository.statistics.impl;

import static com.tuanzi.loan.generated.jooq.loan.tables.LoanApiRecord.LOAN_API_RECORD;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.jooq.Record;
import org.jooq.RecordMapper;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import com.tuanzi.loan.business.repository.statistics.UnionLoginStatRepository;
import com.tuanzi.loan.core.repository.JOOQBaseRepository;
import com.tuanzi.loan.generated.jooq.loan.tables.LoanApiRecord;
import com.tuanzi.loan.vo.request.statistics.UnionLoginStatRequest;
import com.tuanzi.loan.vo.response.statistics.UnionLoginStatResponse;
import com.tuanzi.server.loan_base.util.DateUtils;

@Repository
public class UnionLoginStatRepositoryImpl extends JOOQBaseRepository implements UnionLoginStatRepository {
	
	private final Integer groupByChannle=1;
	
	@Override
	protected LoanApiRecord getTable() {
		return LOAN_API_RECORD;
	}

	@Override
	public Page<UnionLoginStatResponse> list(UnionLoginStatRequest searchRequest, Pageable pageable) {
	
		List<Object> paramList = new ArrayList<Object>();
		Integer isGroupChannel = searchRequest.getGroupChannel();
		if(null==isGroupChannel){
			isGroupChannel=groupByChannle;
		}
		
		StringBuilder sql_before =new StringBuilder();
		StringBuilder sql_after =new StringBuilder();
		
		sql_before.append("(SELECT DATE(ctime) as dateTime,source_id as sourceId,product_id as productId,channel as channelId,platform,order_from as loginType,new_activity_id as newActivityId,activity_type as activityType,environment,userType,page,ck_module as ckModule,position,COUNT(DISTINCT mobnum) as loginNum,COUNT(DISTINCT mobnum,type) as jfNum");
		sql_before.append(" FROM loan_api_record  WHERE 1=1");
		addCondition(sql_before, searchRequest, paramList);
		sql_before.append(" GROUP BY dateTime");
		if(groupByChannle==isGroupChannel){
			sql_before.append(" ,channel ");
		}
		
		sql_before.append(" ) a LEFT JOIN ");	
		sql_after.append("(SELECT DATE(ctime) as dateTime,channel as channelId,COUNT(DISTINCT mobnum) as loginSuccessNum,COUNT(DISTINCT mobnum,type) as jfSuccessNum");
		sql_after.append(" FROM loan_api_record  WHERE result=1 and first_union=1");
		addCondition(sql_after, searchRequest, paramList);
		
		sql_after.append(" GROUP BY dateTime ");
		if(groupByChannle==isGroupChannel){
			sql_after.append(" ,channel ");
		}
		sql_after.append(") b");
		
		StringBuilder sql=new StringBuilder();
		sql.append("SELECT a.*,b.loginSuccessNum,b.jfSuccessNum FROM ");
		sql.append(sql_before.toString());
		sql.append(sql_after.toString());
		sql.append(" ON a.dateTime=b.dateTime ");
		if(groupByChannle==isGroupChannel){
			sql.append(" and a.channelId=b.channelId");
		}
		sql.append(" ORDER BY dateTime DESC ");
		
		String getSql = sql + " LIMIT ?,?";
		paramList.add(pageable.getOffset());
		paramList.add(pageable.getPageSize());
		
		//查询数量sql
		String countSql = "SELECT COUNT(*) AS num FROM (" + sql.toString() + ") AS d";
		Long count = dslContext.fetchOne(countSql, paramList.toArray()).into(Long.class);
		
		List<UnionLoginStatResponse> list = dslContext.fetch(getSql, paramList.toArray()).map(new UnionLoginStatRepositoryRecordMapper());
		
		return new PageImpl<UnionLoginStatResponse>(list, pageable,count);
	}
	
		
	private final class UnionLoginStatRepositoryRecordMapper implements RecordMapper<Record, UnionLoginStatResponse>{

		@Override
		public UnionLoginStatResponse map(Record record) {
			UnionLoginStatResponse response=new UnionLoginStatResponse();
			response.setDateTime(record.get("dateTime",Date.class));
			response.setProductId(record.get("productId",String.class));
			response.setPlatform(record.get("platform",String.class));
			response.setChannelId(record.get("channelId",String.class));
			response.setActivityType(record.get("activityType",String.class));
			response.setEnvironment(record.get("environment",String.class));
			response.setUserType(record.get("userType",String.class));
			response.setPage(record.get("page",String.class));
			response.setCkModule(record.get("ckModule",String.class));
			response.setPosition(record.get("position",String.class));
			response.setSourceId(record.get("sourceId",String.class));
			response.setLoginType(record.get("loginType",String.class));
			response.setLoginNum(record.get("loginNum",Long.class));
			response.setLoginSuccessNum(record.get("loginSuccessNum",Long.class));
			response.setJfNum(record.get("jfNum",Long.class));
			response.setJfSuccessNum(record.get("jfSuccessNum",Long.class));
			return response;
		}
		
	} 
	
	@Override
	public void addCondition(StringBuilder sql,UnionLoginStatRequest searchRequest,List<Object> paramList){
		if(null!=searchRequest.getStartDate()){			
			sql.append(" and ctime>='"+DateUtils.formatDateToYyyyMMdd(searchRequest.getStartDate())+" 00:00:00'");
		}else{
			sql.append(" and ctime>='"+DateUtils.getDateBeforeDays(6)+" 00:00:00'");
		}
			
		if(null!=searchRequest.getEndDate()){
			sql.append(" and ctime<='"+DateUtils.formatDateToYyyyMMdd(searchRequest.getEndDate())+" 23:59:59'");
		}else{
			sql.append(" and ctime<='"+DateUtils.getCurrentDate()+" 23:59:59'");
		}	
		
		if(!StringUtils.isBlank(searchRequest.getPlatform())){
			sql.append(" and platform = '"+searchRequest.getPlatform()+"'");
		}
		if(null!=searchRequest.getProductId()){
			sql.append(" and product_id = "+searchRequest.getProductId()+"");
		}
		if(null!=searchRequest.getChannelId()){
			sql.append(" and channel = "+searchRequest.getChannelId()+"");
		}
		if(null!=searchRequest.getActivityType()){
			sql.append(" and activity_type="+searchRequest.getActivityType()+"");
		}
		if(!StringUtils.isBlank(searchRequest.getActivityId())){
			sql.append(" and new_activity_id='"+searchRequest.getActivityId()+"'");
		}
		if(null!=searchRequest.getEnvironment()){
			sql.append(" and environment = '"+searchRequest.getEnvironment()+"'");
		}
		if(!StringUtils.isBlank(searchRequest.getPage())){
			sql.append(" and page = '"+searchRequest.getPage()+"'");
		}
		if(!StringUtils.isBlank(searchRequest.getCkModule())){
			sql.append(" and ck_module = '"+searchRequest.getCkModule()+"'");
		}
		if(!StringUtils.isBlank(searchRequest.getPosition())){
			sql.append(" and position = '"+searchRequest.getPosition()+"'");
		}
		if(null!=searchRequest.getSourceId()){
			sql.append(" and source_id = "+ searchRequest.getSourceId()+"");
		}
		if(null!=searchRequest.getLoginType()){
			sql.append(" and order_from = '"+ searchRequest.getLoginType()+"'");
		}
		if(!StringUtils.isBlank(searchRequest.getUserType())){
			sql.append(" and userType = '"+ searchRequest.getUserType()+"'");
		}	
	}

}
